﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace SSISFromCode.Helpers
{

    /// <summary>
    /// Accepts an open connection and executes the SQL to create our global temp table therein
    /// </summary>
    internal static class TableCreator
    {

        public static string Execute( List<string> columns, SqlConnection openConnection, int columnWidth )
        {
            var baseTableName = Guid.NewGuid().ToString().Replace( "-", string.Empty );  // table name w/o the "##"
            var result = string.Format( DESTINATION_TABLE.TABLE_NAME, baseTableName );
            var columnDefinition = CreateSQL( baseTableName, columns, columnWidth );
            TableCreator.ExecuteStatement( string.Format( DESTINATION_TABLE.CREATE_TABLE, result, columnDefinition.ToString() ), openConnection );
            return result;
        }

        private static StringBuilder CreateSQL( string baseTableName, List<string> columns, int columnWidth )
        {
            var columnDefinition = new StringBuilder();
            columnDefinition.Append( string.Format( DESTINATION_TABLE.CREATE_PRIMARY_KEY_COLUMN, baseTableName ) );
            for ( int i = 0; i < columns.Count; i++ )
            {
                columnDefinition.Append( string.Format( DESTINATION_TABLE.CREATE_COLUMN, columns[i], columnWidth ) );
                if ( i != ( columns.Count - 1 ) )
                {
                    columnDefinition.Append( DELIMITERS.COMMA.ToString() );
                }
            }
            return columnDefinition;
        }

        private static void ExecuteStatement( string sql, SqlConnection openConnection )
        {
            var cmd = new SqlCommand( sql, openConnection );
            cmd.ExecuteNonQuery();
        }

    }

}
